package com.tianqu.tms.www.statistics.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TmsTableNames;
import com.tianqu.tms.www.statistics.dto.ComboDto;
import com.tianqu.tms.www.statistics.dto.TradeTransferMonthCountDto;

public class TmsTransferMonthCountDao extends TmsDao{

	public TmsTransferMonthCountDao() throws TmsDaoException {
		super();
	}

	/**
	 * 获得最近十个月交易过户量(top9)
	 * @return
	 * @throws TmsDaoException
	 */
	public List<ComboDto> getLastMonthsTransferNumTop9() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//前九个月
			StringBuffer sql = new StringBuffer(" SELECT");
			sql.append(" concat(countY,LPAD(countM, 2, 0)) as resDate , transferNum ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_MONTH_COUNT);
			sql.append(" ORDER BY resDate DESC limit 0,9");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			List<ComboDto> list = new ArrayList<ComboDto>();
			while(rs.next()) {
				ComboDto dto = new ComboDto();
				dto.setName(rs.getString("resDate"));
				dto.setValue(rs.getString("transferNum"));
				list.add(dto);
			}
			return list;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	/**
	 * 获得最近十个月交易过户量(top1)
	 * @return
	 * @throws TmsDaoException
	 */
	public ComboDto getLastMonthsTransferNumTop1() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		ComboDto dto = new ComboDto();
		try {
			
			StringBuffer sql = new StringBuffer(" SELECT");
			sql.append(" concat(date_format (now(),'%Y'),date_format (now(),'%m'))as resDate ,");
			sql.append(" IFNULL(sum(transferNum), 0) as transferNum");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_DAY_COUNT);
			sql.append(" WHERE countM = date_format(now(),'%m') and countY = date_format (now(),'%Y')");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if(rs.next()) {
				dto.setName(rs.getString("resDate"));
				dto.setValue(rs.getString("transferNum"));
			}
			return dto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
}
